package com.ljz.dao.daoImpl;

import com.ljz.dao.EmpDao;
import com.ljz.entity.Emp;
import com.ljz.untils.DBUtils2;
import com.ljz.untils.DBUtils3;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmpDaoImpl implements EmpDao {
    @Override
    public List<Emp> findAll() {
        String sql = "select * from Emp";
        Connection conn = DBUtils2.getConn();
        PreparedStatement ps = null;
        ResultSet resultSet =null;
        List<Emp> list = new ArrayList<>();
        try {
            if (conn != null) {
                ps = conn.prepareStatement(sql);
            }
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                Emp emp = new Emp();
                emp.setEmpno(resultSet.getInt("empno"));
                emp.setEname(resultSet.getString("ename"));
                emp.setJob(resultSet.getString("job"));
                emp.setComm(resultSet.getInt("comm"));
                emp.setHiredate(resultSet.getDate("hiredate"));
                emp.setSal(resultSet.getDouble("sal"));
                emp.setMgr(resultSet.getInt("mgr"));
                emp.setDeptno(resultSet.getInt("deptno"));
                list.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils2.closeAll(resultSet, ps, conn);
        }
        return list;
    }

    @Override
    public Emp findById(int empno) {
        String sql = "select * from Emp where empno = ?";
        Connection conn = DBUtils2.getConn();
        PreparedStatement ps = null;
        ResultSet resultSet =null;
       Emp emp = new Emp();
        try {
            if (conn != null) {
                ps = conn.prepareStatement(sql);
            }
            ps.setInt(1, empno);
            resultSet = ps.executeQuery();
            resultSet.next();
            emp.setEmpno(resultSet.getInt("empno"));
            emp.setEname(resultSet.getString("ename"));
            emp.setJob(resultSet.getString("job"));
            emp.setComm(resultSet.getInt("comm"));
            emp.setHiredate(resultSet.getDate("hiredate"));
            emp.setSal(resultSet.getDouble("sal"));
            emp.setMgr(resultSet.getInt("mgr"));
            emp.setDeptno(resultSet.getInt("deptno"));
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
           DBUtils2.closeAll(resultSet, ps, conn);
        }
        return emp;
    }

    @Override
    public int add(Emp emp) {
        String sql = "insert into emp(ename,job,sal) values(?,?,?)";
        List<Object> list = new ArrayList<>();
        list.add(emp.getEname());
        list.add(emp.getJob());
        list.add(emp.getSal());
        int num = DBUtils3.exceUpdate(sql, list);
        return num;
    }

    @Override
    public int update(Emp emp) {
        String sql = "update emp set ename=?,job=?,sal=? where empno=?";
        List<Object> list = new ArrayList<>();
        list.add(emp.getEname());
        list.add(emp.getJob());
        list.add(emp.getSal());
        list.add(emp.getEmpno());
        int num = DBUtils2.exceUpdate(sql, list);
        return num;
    }

    @Override
    public int delete(int empno) {
        String sql = "delete from emp where empno = ?";
        List<Object> list = new ArrayList<>();
        list.add(empno);
        int num = DBUtils2.exceUpdate(sql, list);
        return num;
    }
}
